<?php
/**
 * Comprehensive Code Scanner
 * Scan all PHP files for potential table name references
 * 
 * Usage: php scan_all_table_references.php
 */

echo "🔍 Scanning all PHP files for table name references...\n\n";

$baseDir = __DIR__ . '/../../..';
$targetTables = [
    'apps', 'merchants', 'users', 'anti_fraud', 'api_keys', 'attribution',
    'et_apps', 'et_apps_apps', 'et_apps_event', 'et_apps_web',
    'et_system_email', 'et_system_email_config', 'et_users',
    'fingerprints', 'stats_daily', 'merchant_api_keys', 'merchant_stats'
];

$scanResults = [
    'Db_table_calls' => [],
    'table_property' => [],
    'raw_sql' => [],
    'config_files' => []
];

$totalFilesScanned = 0;
$totalMatches = 0;

function scanDirectory($dir, $targetTables, &$scanResults, &$totalFilesScanned, &$totalMatches) {
    $files = glob($dir . '/**/*.php');
    
    foreach ($files as $file) {
        // Skip vendor directory
        if (strpos($file, '/vendor/') !== false) {
            continue;
        }
        
        $content = file_get_contents($file);
        $fileMatches = [];
        $relativePath = str_replace(__DIR__ . '/../../..', '', $file);
        
        // Scan for Db::table() calls
        foreach ($targetTables as $table) {
            // Pattern for Db::table('table_name')
            if (preg_match_all("/Db::table\(['\"]" . preg_quote($table, '/') . "['\"]\)/", $content, $matches)) {
                $fileMatches['Db_table_calls'][$table] = count($matches[0]);
            }
            
            // Pattern for \think\facade\Db::table('table_name')
            if (preg_match_all("/\\\\think\\\\facade\\\\Db::table\(['\"]" . preg_quote($table, '/') . "['\"]\)/", $content, $matches)) {
                $fileMatches['Db_table_calls'][$table] = ($fileMatches['Db_table_calls'][$table] ?? 0) + count($matches[0]);
            }
            
            // Pattern for protected $table = 'table_name'
            if (preg_match_all("/protected\s+\$table\s*=\s*['\"]" . preg_quote($table, '/') . "['\"]/", $content, $matches)) {
                $fileMatches['table_property'][$table] = count($matches[0]);
            }
            
            // Pattern for raw SQL table references
            if (preg_match_all("/FROM\s+`?" . preg_quote($table, '/') . "`?/i", $content, $matches)) {
                $fileMatches['raw_sql'][$table] = ($fileMatches['raw_sql'][$table] ?? 0) + count($matches[0]);
            }
            if (preg_match_all("/JOIN\s+`?" . preg_quote($table, '/') . "`?/i", $content, $matches)) {
                $fileMatches['raw_sql'][$table] = ($fileMatches['raw_sql'][$table] ?? 0) + count($matches[0]);
            }
            if (preg_match_all("/INSERT\s+INTO\s+`?" . preg_quote($table, '/') . "`?/i", $content, $matches)) {
                $fileMatches['raw_sql'][$table] = ($fileMatches['raw_sql'][$table] ?? 0) + count($matches[0]);
            }
            if (preg_match_all("/UPDATE\s+`?" . preg_quote($table, '/') . "`?/i", $content, $matches)) {
                $fileMatches['raw_sql'][$table] = ($fileMatches['raw_sql'][$table] ?? 0) + count($matches[0]);
            }
        }
        
        // Only add to results if we found matches
        $hasMatches = false;
        foreach ($fileMatches as $category => $tables) {
            if (!empty($tables)) {
                $hasMatches = true;
                foreach ($tables as $table => $count) {
                    $totalMatches += $count;
                    if (!isset($scanResults[$category][$table])) {
                        $scanResults[$category][$table] = [];
                    }
                    $scanResults[$category][$table][] = [
                        'file' => $relativePath,
                        'count' => $count
                    ];
                }
            }
        }
        
        if ($hasMatches) {
            echo "📄 Found matches in: $relativePath\n";
            foreach ($fileMatches as $category => $tables) {
                if (!empty($tables)) {
                    echo "  $category: ";
                    foreach ($tables as $table => $count) {
                        echo "$table($count) ";
                    }
                    echo "\n";
                }
            }
            echo "\n";
        }
        
        $totalFilesScanned++;
    }
}

// Scan the webman directory
scanDirectory($baseDir . '/webman', $targetTables, $scanResults, $totalFilesScanned, $totalMatches);

echo "📊 SCAN SUMMARY:\n";
echo "==================================================\n";
echo "Total files scanned: $totalFilesScanned\n";
echo "Total matches found: $totalMatches\n";
echo "Files with matches: " . count(array_unique(array_merge(
    array_column($scanResults['Db_table_calls'], 'file'),
    array_column($scanResults['table_property'], 'file'),
    array_column($scanResults['raw_sql'], 'file')
))) . "\n\n";

// Detailed results by category
echo "🔍 DETAILED RESULTS:\n";
echo "==================================================\n\n";

foreach ($scanResults as $category => $tables) {
    if (!empty($tables)) {
        echo "🏷️  $category:\n";
        foreach ($tables as $table => $files) {
            echo "  📋 $table:\n";
            foreach ($files as $fileInfo) {
                echo "    - {$fileInfo['file']} ({$fileInfo['count']} occurrences)\n";
            }
            echo "\n";
        }
    }
}

// Generate update script
echo "📝 GENERATING UPDATE SCRIPT...\n";
generateUpdateScript($scanResults);

echo "✅ Scan completed! Check update_table_references.php for the generated update script.\n";

function generateUpdateScript($results) {
    $scriptContent = "<?php\n";
    $scriptContent .= "/**\n";
    $scriptContent .= " * Auto-generated table reference update script\n";
    $scriptContent .= " * Generated at: " . date('Y-m-d H:i:s') . "\n";
    $scriptContent .= " */\n\n";
    
    $scriptContent .= "\$tableMappings = [\n";
    
    // Collect all tables that need updating
    $tablesToUpdate = [];
    foreach ($results as $category => $tables) {
        foreach ($tables as $table => $files) {
            if (!isset($tablesToUpdate[$table])) {
                $tablesToUpdate[$table] = "gf_" . $table;
            }
        }
    }
    
    foreach ($tablesToUpdate as $oldTable => $newTable) {
        $scriptContent .= "    '$oldTable' => '$newTable',\n";
    }
    
    $scriptContent .= "];\n\n";
    
    $scriptContent .= "// Update script implementation...\n";
    $scriptContent .= "// This script will update all table references in your codebase\n";
    
    file_put_contents('update_table_references.php', $scriptContent);
    echo "  ✅ Update script generated: update_table_references.php\n";
}

?>